﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DX.CRC.Common.Entities;
using StuMngWx.Entities;

namespace StuMngWx.Dals
{
    class Dal4Teachers : DalBase
    {
        [Obsolete(@"【闻祖东 2013-2-4-231253】不再使用DataTable作为返回值，虽然这样也可以进行绑定列表。但是这样始终没有绑定List<Teacher>这样来的
            更加面向对象。")]
        public static DataTable SearchAllTeachers()
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"SELECT [ListID]
	            ,[Name]
	            ,[Age]
	            ,[Sex]
	            ,[Course]
            FROM [dbo].[Teacher] a
            where a.IsActive = 1;";

            return qo.xExecuteDataTable();
        }

        public static List<Teacher> SearchAllTeachersNew()
        {
            ///【闻祖东 2013-2-4-231614】注意这里为什么可以访问CreateQO()这个东东？CreateQO()这个方法是父类DalBase的Protected成员，那么
            ///DalBase的子类是可以访问CreateQO()这个成员的。
            QueryObject qo = CreateQO();

            qo.CmdText = @"SELECT [ListID] as ID
	            ,[Name]
	            ,[Age]
                ,[Address]
	            ,[Sex] as Sex_Field
	            ,[Course] as CourseType_Field
            FROM [dbo].[Teacher] a
            where a.IsActive = 1;";

            return qo.xExecuteList<Teacher>();
        }

        [Obsolete("【闻祖东 2013-2-4-225657】此方法已作废，请使用使用Teacher类作为参数的方法。")]
        public static void AddTeacher(string name, string address, bool sex, int age, int courseID, string graduateFrom)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"INSERT INTO [dbo].[Teacher]
                   ([Name]
                   ,[Age]
                   ,[Sex]
                   ,[CourseID]
                   ,[GraduatedFrom]
                   ,[IsActive]
                   ,[CreateTime]
                   ,[Remark])
             VALUES
                   (@Name
                   ,@Age
                   ,@Sex
                   ,@CourseID
                   ,@GraduatedFrom
                   ,1
                   ,getutcdate()
                   ,N'');";

            qo.AddParam("@Name", name);
            qo.AddParam("@Age", age);
            qo.AddParam("@Sex", sex);
            qo.AddParam("@CourseID", courseID);
            qo.AddParam("@GraduatedFrom", graduateFrom);

            qo.xExecuteNonQuery();
        }

        public static void AddTeacher(Teacher teacher)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"INSERT INTO [dbo].[Teacher]
                   ([Name]
                   ,[Age]
                   ,[Sex]
                   ,[Address]
                   ,[Course]
                   ,[GraduatedFrom]
                   ,[IsActive]
                   ,[CreateTime]
                   ,[Remark])
             VALUES
                   (@Name
                   ,@Age
                   ,@Sex
                   ,@Address
                   ,@Course
                   ,@GraduatedFrom
                   ,1
                   ,getutcdate()
                   ,N'');";

            qo.AddParam("@Name", teacher.Name);
            qo.AddParam("@Age", teacher.Age);
            qo.AddParam("@Sex", teacher.Sex);
            qo.AddParam("@Address", teacher.Address);
            qo.AddParam("@Course", teacher.CourseType);
            qo.AddParam("@GraduatedFrom", teacher.GraduateFrom);

            qo.xExecuteNonQuery();
        }

        public static void DeleteTeacher(Teacher teacher)
        {
            QueryObject qo = CreateQO();

            qo.CmdText = @"update Teacher 
                set IsActive = 0 
                where ListID = @TeacherID";

            qo.AddParam("@TeacherID", teacher.ID);

            qo.xExecuteNonQuery();
        }

        public static DataTable GetCourses()
        {
            QueryObject qo = CreateQO();
            qo.CmdText = @"select ListID as CourseID
	            , Name as CourseName
            from Course
            where IsActive = 1";

            return qo.xExecuteDataTable();
        }
    }
}
